

'''
-- 创建角色表
CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);
'''

from . import logger

class RolesTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor
    # 创建角色
    def create_role(self, role_name, description):
        
        try:
            self.cursor.execute("INSERT INTO roles (role_name, description) VALUES (%s, %s)", (role_name, description))

        except Exception as e:
            logger.error("Error creating role: {}".format(e))

        else:

            logger.info(f"Role {role_name} created successfully.")


    # 根据角色id获取角色信息
    def get_role_by_id(self, role_id):
        try:
            self.cursor.execute("SELECT * FROM roles WHERE role_id = %s", (role_id,))
            role = self.cursor.fetchone()

        except Exception as e:
            logger.error("Error getting role by id: {}".format(e))
            role = None

        else:
            logger.info(f"Role {role_id} retrieved successfully.")

        return role
    
    # 根据角色名获取角色信息
    def get_role_by_name(self, role_name):
        try:
            self.cursor.execute("SELECT * FROM roles WHERE role_name = %s", (role_name,))
            role = self.cursor.fetchone()

        except Exception as e:
            logger.error("Error getting role by name: {}".format(e))
            role = None

        else:
            logger.info(f"Role {role_name} retrieved successfully.")

        return role
    
    # 更新角色信息
    def update_role(self, role_id, role_name, description):
        try:
            self.cursor.execute("UPDATE roles SET role_name = %s, description = %s WHERE role_id = %s", (role_name, description, role_id))

        except Exception as e:
            logger.error("Error updating role: {}".format(e))

            return False, e

        else:

            logger.info(f"Role {role_id} updated successfully.")
            return True, None

    # 删除角色
    def delete_role(self, role_id):
        try:
            self.cursor.execute("DELETE FROM roles WHERE role_id = %s", (role_id,))

        except Exception as e:
            logger.error("Error deleting role: {}".format(e))
        else:
            logger.info(f"Role {role_id} deleted successfully.")

    # 获取所有角色信息
    def get_all_roles(self, offset=0, limit=10):
        try:
            self.cursor.execute("SELECT * FROM roles ORDER BY role_id DESC LIMIT %s OFFSET %s", (limit, offset))
            roles = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting all roles: {}".format(e))
            roles = []

        else:
            logger.info(f"All roles retrieved successfully.")

        return roles
    
    # 获取角色总数
    def get_role_count(self):
        try:
            self.cursor.execute("SELECT COUNT(*) AS count FROM roles")
            count = self.cursor.fetchone()["count"]

        except Exception as e:
            logger.error("Error getting role count: {}".format(e))
            count = 0

        else:
            logger.info(f"Role count retrieved successfully.")

        return count